const pool = require('../config/db');
const { v4: uuidv4 } = require('uuid');

const Post = {
    async create({ user_id, content, image_url, video_url, location, hashtags }) {
        const post_id = uuidv4();
        const connection = await pool.getConnection();

        try {
            await connection.beginTransaction();

            await connection.query(
                'INSERT INTO posts (post_id, user_id, content, image_url, video_url, location) VALUES (?, ?, ?, ?, ?, ?)',
                [post_id, user_id, content, image_url, video_url, location]
            );

            await connection.query('INSERT INTO post_stats (post_id) VALUES (?)', [post_id]);

            await connection.query(
                'UPDATE user_stats SET posts_count = posts_count + 1 WHERE user_id = ?',
                [user_id]
            );

            if (hashtags && hashtags.length > 0) {
                for (const tag of hashtags) {
                    let hashtag_id;
                    const [existing] = await connection.query('SELECT hashtag_id FROM hashtags WHERE tag = ?', [tag]);

                    if (existing.length > 0) {
                        hashtag_id = existing[0].hashtag_id;
                    } else {
                        hashtag_id = uuidv4();
                        await connection.query('INSERT INTO hashtags (hashtag_id, tag) VALUES (?, ?)', [hashtag_id, tag]);
                    }

                    await connection.query('INSERT INTO post_hashtags (post_id, hashtag_id) VALUES (?, ?)', [post_id, hashtag_id]);
                }
            }

            await connection.commit();
            return { post_id };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    },

    async findById(post_id) {
        const [rows] = await pool.query(
            'SELECT p.*, ps.likes_count, ps.comments_count, ps.shares_count FROM posts p LEFT JOIN post_stats ps ON p.post_id = ps.post_id WHERE p.post_id = ? AND p.is_active = TRUE',
            [post_id]
        );
        return rows[0];
    },

    async findByUserId(user_id, limit = 10, offset = 0) {
        const [rows] = await pool.query(
            'SELECT p.*, ps.likes_count, ps.comments_count, ps.shares_count FROM posts p LEFT JOIN post_stats ps ON p.post_id = ps.post_id WHERE p.user_id = ? AND p.is_active = TRUE ORDER BY p.created_at DESC LIMIT ? OFFSET ?',
            [user_id, limit, offset]
        );
        return rows;
    }
};

module.exports = Post;